ADOX is an extension to the ADO library, exposing the catalog of database objects. ADOX exposes additional objects for creating, modifying and deleting schema objects such as tables and procedures. It also includes security objects to maintain users and groups and to grant and revoke permissions on objects. To open the References dialog box from the Tools menu and select your latest version of Microsoft ADO Ext. 6.0 for DDL and security.

In this article we are creating index prevents duplicate values from being entered into the combination of these columns using ADOX. Sometimes there is need to store unique data across multiple columns in a table. For that there is need to create composite unique index, that prevents the duplicate value in a table. For implementation of composite unique index using ADOX, we need to add the reference as shown in Fig 1.1 and write code for that. In implementation we are using ADOX catalog. To support the creation of a database, the ADOX library provides the Catalog class. To use it, declare a variable of type Catalog and qualify it as ADOX.Catalog.

After coding part we have to debug the code. With successful debugging a message will pop up which indicate composite unique index is created as shown in Fig 1.2.

Now we have to test the composite unique index in table, for that type same values in col_2 and col_3. When we try to save this table, a warning message will pop up which do not allow duplicate values in table as shown in Fig 1.3.

VBA code

Option Compare Database
Private Sub Command0_Click()
Dim con As ADODB.Connection
Dim idx As ADOX.Index
Dim clog As ADOX.Catalog
Dim tbl As ADOX.Table
Set con = CurrentProject.Connection
Set clog = New ADOX.Catalog
Set clog.ActiveConnection = con
Set tbl = New ADOX.Table
tbl.Name = "Table1"
tbl.Columns.Append "ID", adInteger
tbl.Columns.Append "col_2", adVarWChar, 200
tbl.Columns.Append "col_3", adInteger
tbl.Keys.Append "PrimaryKey", adKeyPrimary, "ID"
Set tbl.Columns.Item("ID").ParentCatalog = clog
tbl.Columns.Item("ID").Properties ("Autoincrement") = True
clog.Tables.Append tbl
Set idx = New ADOX.Index
idx.Name = "Uidx_Products"
idx.IndexNulls = adIndexNullsAllow
idx.PrimaryKey = False
idx.Unique = True
idx.Columns.Append "col_2"
idx.Columns.Append "col_3"
tbl.Indexes.Append idx
con.Execute "INSERT INTO Table1(col_2, col_3) values ('value1', 1)"
con.Execute "INSERT INTO Table1(col_2, col_3) values ('value2', 2)"
con.Execute "INSERT INTO Table1(col_2, col_3) values ('value3', 2)"
con.Execute "INSERT INTO Table1(col_2, col_3) values ('value3', 3)"
con.Close
MsgBox "composite unique index created"
Exit Sub
End Sub